package HelperFinancial.DataBase;

import HelperFinancial.Class.Debito;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DebitoHelper extends SQLiteOpenHelper {
	
	private static final String DATABASE_NAME="debitos.db";
	private static final int SCHEMA_VERSION=1;
	
	public DebitoHelper(Context context) {
		super(context, DATABASE_NAME, null, SCHEMA_VERSION);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE debits (_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, value REAL, portions INTEGER, " +
				   						 "type TEXT, date TEXT, notes TEXT, corrente INTEGER, pai INTEGER);");
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}
	
	public void insertDebit(Debito debito) {
			ContentValues cv = new ContentValues();
			cv.put("name", debito.getName());
			cv.put("value", debito.getValue());
			cv.put("portions", debito.getPortions());
			cv.put("type", debito.getType());
			cv.put("date", debito.getDate());
			cv.put("notes", debito.getNotes());
			cv.put("corrente", debito.getParcelaCorrente());
			cv.put("pai", debito.getIdPai());
			getWritableDatabase().insert("debits", "name", cv);
	}
	
	public Cursor getAllDebits() {
		return(getReadableDatabase()
		.rawQuery("SELECT * FROM debits ORDER BY date GROUP BY type",
		null));
	}
	
	
	public Cursor getDebitByName(String name){
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM debits WHERE name = '" + name + "'",
				null));
	}
	
	public Cursor getDebitById(Integer id){
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM debits WHERE _id = " + id,
				null));
	}
	
	
	public Cursor getAllDebitByType(){
		return(getReadableDatabase()
				.rawQuery("SELECT type, SUM(value) AS valueTotal FROM debits GROUP BY type",
				null));
	}
	
	public Cursor getAllDebitByTypeToGraphic(String date){
		return(getReadableDatabase()
				.rawQuery("SELECT type, SUM(value) AS valueTotal FROM debits WHERE SUBSTR(date, 4) = '" + date + "' GROUP BY type",
				null));
	}
	
	public Cursor getDebitsSum(){
		return(getReadableDatabase()
				.rawQuery("SELECT SUM(value) AS valueTotal FROM debits",
				null));
	}
	
	public Cursor getDebitsSumToGraphic(String date){
		return(getReadableDatabase()
				.rawQuery("SELECT SUM(value) AS valueTotal FROM debits WHERE SUBSTR(date, 4) = '" + date + "'",
				null));
	}
	
	public Cursor getDebitsSumByDate(String date){
		return(getReadableDatabase()
				.rawQuery("SELECT SUM(value) AS valueTotal FROM debits WHERE date LIKE '%" + date +
						  "%'",	null));
	}
	
	public Cursor getDebitsIds() {
		return(getReadableDatabase()
				.rawQuery("SELECT MAX(_id) AS maiorId FROM debits",	null));
	}
	
	public int getLastId() {
		Cursor cr = getDebitsIds();
		
		int colValue = cr.getColumnIndex("maiorId");
		
		cr.moveToFirst();
		int value = cr.getInt(colValue);
		
		return value;
	}
	
	public void removeAllDebits() {
		getReadableDatabase().delete("debits", null, null);
	}
	
	public void removeDebit(Integer Id){
		getReadableDatabase().delete("debits", "_id=" + Id, null);
	}
	
	public void updateDebit(Debito debito){
		ContentValues cv = new ContentValues();
		cv.put("name", debito.getName());
		cv.put("value", debito.getValue());
		cv.put("portions", debito.getPortions());
		cv.put("type", debito.getType());
		cv.put("date", debito.getDate());
		cv.put("notes", debito.getNotes());
		cv.put("corrente", debito.getParcelaCorrente());
		cv.put("pai", debito.getIdPai());
		
		getReadableDatabase().update("debits", cv, "_id=" + debito.getId(), null);
	}
	
	public void updateDebitIdPai(Debito debito, int id){
		ContentValues cv = new ContentValues();
		cv.put("name", debito.getName());
		cv.put("value", debito.getValue());
		cv.put("portions", debito.getPortions());
		cv.put("type", debito.getType());
		cv.put("date", debito.getDate());
		cv.put("notes", debito.getNotes());
		cv.put("corrente", debito.getParcelaCorrente());
		cv.put("pai", debito.getIdPai());
		
		getReadableDatabase().update("debits", cv, "_id=" + id, null);
	}
	
	public String getDebitName(Cursor c) {
		return(c.getString(1));
	}
	
	public double getDebitValue(Cursor c) {
		return(c.getDouble(2));
	}
	
	public int getDebitPortions(Cursor c) {
		return(c.getInt(3));
	}
	
	public String getDebitType(Cursor c) {
		return(c.getString(4));
	}
	
	public String getDebitDate(Cursor c) {
		return(c.getString(5));
	}
	
	public String getDebitNotes(Cursor c) {
		return(c.getString(6));
	}
	
	public int getDebitParcelaCorrente(Cursor c) {
		return(c.getInt(7));
	}
	
	public int getDebitIdPai(Cursor c) {
		return(c.getInt(8));
	}
	
	public Cursor getFilhosHabilitados(int parcelaCorrente, int idPai) {
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM debits WHERE corrente>=" + parcelaCorrente + " and pai=" + idPai, null));
	}
	
	public Cursor getDebitsByFilter(String date){
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM debits WHERE date LIKE '%" + date +
						  "%' ORDER BY type", null));
	}
	
	public Cursor getDebitsByFilter(String date, String type){
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM debits WHERE date LIKE '%" + date +
						  "%' AND type = '" + type + "' ORDER BY type", null));
	}
	
	public Cursor getDebitsByDate(String date){
		return(getReadableDatabase()
				.rawQuery("SELECT * FROM debits WHERE date = '" + date +
						  "' ORDER BY type", null));
	}

}
